import pymysql
from pymysql.cursors import DictCursor
import sys
import os

# 将项目根目录添加到Python路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

# 从app.py中导入配置
try:
    from app import app
    # 从app.config中直接获取数据库配置
    db_uri = app.config['SQLALCHEMY_DATABASE_URI']
except Exception as e:
    print(f"无法从app.py导入配置: {e}")
    print("使用默认配置...")
    # 使用默认配置
    db_uri = 'mysql+pymysql://root:123456@localhost:3306/mobile_test_platform'

def update_users_table():
    """更新users表，添加permission_level列"""
    try:
        # 解析数据库连接字符串
        # 格式: mysql+pymysql://username:password@host:port/dbname
        parts = db_uri.split('://')[1].split('@')
        user_pass = parts[0].split(':')
        host_port_db = parts[1].split('/')
        host_port = host_port_db[0].split(':')
        
        username = user_pass[0]
        password = user_pass[1] if len(user_pass) > 1 else ''
        host = host_port[0]
        port = int(host_port[1]) if len(host_port) > 1 else 3306
        dbname = host_port_db[1]
        
        # 连接数据库
        conn = pymysql.connect(
            host=host,
            port=port,
            user=username,
            password=password,
            db=dbname,
            charset='utf8mb4',
            cursorclass=DictCursor
        )
        cursor = conn.cursor()
        
        print(f"正在连接数据库: {host}:{port} - {dbname}")
        
        # 检查users表是否存在
        cursor.execute("SHOW TABLES LIKE 'users'")
        if not cursor.fetchone():
            print("users表不存在，正在创建...")
            # 创建users表
            create_users_table = '''
            CREATE TABLE users (
                id INT PRIMARY KEY AUTO_INCREMENT,
                phone VARCHAR(20) NOT NULL UNIQUE,
                password_hash VARCHAR(255) NOT NULL,
                username VARCHAR(100),
                avatar VARCHAR(255),
                is_active BOOLEAN DEFAULT TRUE,
                last_login_at DATETIME,
                permission_level INT DEFAULT 0 NOT NULL,
                wechat_openid VARCHAR(100) UNIQUE,
                wechat_unionid VARCHAR(100) UNIQUE,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            );
            '''
            cursor.execute(create_users_table)
            print("✅ users表创建成功！")
        else:
            # 检查permission_level列是否存在
            cursor.execute("SHOW COLUMNS FROM users LIKE 'permission_level'")
            if not cursor.fetchone():
                print("正在添加permission_level列到users表...")
                alter_table_sql = "ALTER TABLE users ADD COLUMN permission_level INT DEFAULT 0 NOT NULL;"
                cursor.execute(alter_table_sql)
                print("✅ permission_level列添加成功！")
            else:
                print("✅ permission_level列已存在，无需添加。")
        
        conn.commit()
        return True
        
    except pymysql.MySQLError as e:
        print(f"❌ MySQL操作失败: {e}")
        return False
    finally:
        # 关闭连接
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

def main():
    print("====== 数据库表结构更新工具 ======")
    print("此工具将更新users表，添加permission_level列。")
    
    if update_users_table():
        print("\n✅ 数据库表结构更新成功！")
    else:
        print("\n❌ 数据库表结构更新失败！")

if __name__ == "__main__":
    main()